package com.classes;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.TreeMap;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.JSONValue;

import com.mbbmap.app.dao.UserDao;
import com.mbbmap.app.home.SecGroupHome;
import com.mbbmap.app.home.SecUsersHome;
import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.security.dao.SecGroupsDao;
import com.mbbmap.util.Constants;

public class Casemast {
	private long id = 0;
	private String group_code = "";
	private String group_name = "";
	private String type = "";
	private String action = "";
	private String status = "";
	private String action2 = "";
	private String status2 = "";
	private String branch2 = "";
	private String acquirer = "";
	private String reference_no = "";
	private String last_reference_no = "";
	private String country_code = "";
	private String document_checklist = "";
	private String best_practice = "";
	private Merchant merchant;
	private long created_date;
	private String created_by;
	private long submit_date;
	private String external_remark = "";
	private String external_ref_no = "";
	private String external_code = "";
	private String exported_external_code = "";
	private String reseller_location = "";
	private ArrayList<TreeMap<String, String>> histories;
	private ArrayList<TreeMap<String, String>> histories2;
	private ArrayList<TreeMap<String, String>> documents;
	private ArrayList<CaseOutlet> outlets;
	private JSONArray recommendations;
	
	public static String formatCreatedDate(String fmt, long created_dt){
		SimpleDateFormat df = new SimpleDateFormat(fmt);
		Date dt = new Date(created_dt);
		
		return df.format(dt);
	}
	
	public long getId() {
		return this.id;
	}
	
	public String getGroupCode() {
		return this.group_code;
	}
	
	public String getGroupName() {
		return this.group_name;
	}
	
	public String getType() {
		return this.type;
	}
	
	public String getAction() {
		return this.action;
	}
	
	public String getStatus() {
		return this.status;
	}
	
	public String getAction2() {
		return this.action2;
	}
	
	public String getStatus2() {
		return this.status2;
	}
	
	public String getBranch2() {
		return this.branch2;
	}
	
	public String getAcquirer() {
		return this.acquirer;
	}
	
	public Merchant getMerchant() {
		return this.merchant;
	}
	
	public long getCreatedDate(){
		return this.created_date;
	}
	
	public long getSubmitDate(){
		return this.submit_date;
	}
	
	public String getCreatedBy() {
		return this.created_by;
	}
	
	public String getReferenceNo() {
		return this.reference_no;
	}
	
	public String getLastReferenceNo() {
		return this.last_reference_no;
	}
	
	public String getCountryCode() {
		return this.country_code;
	}
	
	public String getDocumentChecklist() {
		return this.document_checklist;
	}
	
	public String getBestPractice() {
		return this.best_practice;
	}
	
	public String getExternalRemark() {
		return this.external_remark;
	}
	
	public String getExternalRefNo() {
		return this.external_ref_no;
	}
	
	public String getExternalCode() {
		return this.external_code;
	}
	
	public String getExportedExternalCode() {
		return this.exported_external_code;
	}
	
	public String getResellerLocation() {
		return this.reseller_location;
	}
	
	public ArrayList<CaseOutlet> getOutlets(){
		return this.outlets;
	}
	
	public ArrayList<TreeMap<String, String>> getHistories(){
		return this.histories;
	}
	
	public ArrayList<TreeMap<String, String>> getHistories2(){
		return this.histories2;
	}
	
	public ArrayList<TreeMap<String, String>> getDocuments(){
		return this.documents;
	}
	
	public JSONArray getRecommendations(){
		return this.recommendations;
	}
	
	public TreeMap<String, String> getDocument(long doc_id){
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select cd_id, cd_name, cd_type, cd_length, cd_created_by, cd_created_date "
					+ "from casedoc where cd_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, doc_id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							TreeMap<String, String> docLog = new TreeMap<String, String>();
							docLog.put("id", resultSet.getString("cd_id"));
							docLog.put("name", resultSet.getString("cd_name"));
							docLog.put("type", resultSet.getString("cd_type"));
							docLog.put("length", resultSet.getString("cd_length"));
							docLog.put("created_by", resultSet.getString("cd_created_by"));
							docLog.put("created_date", Long.toString(((Timestamp) resultSet.getTimestamp("cd_created_date")).getTime()));
							
							return docLog;
						}
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch(Exception e){
			e.printStackTrace();
		}
		
		return null;
	}
	
	public byte[] getDocumentBytes(long doc_id){
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select cd_data from casedoc where cd_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, doc_id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							Blob b = resultSet.getBlob("cd_data");
							byte[] data = b.getBytes(1, (int) b.length());
							
							return data;
						}
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch(Exception e){
			e.printStackTrace();
		}
		
		return null;
	}
	
	public void loadOutlets(){
		this.loadOutlets(this.id);
	}
	
	public void loadOutlets(long case_id){
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from caseoutlet where co_case_id = ? order by co_outlet_id asc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, case_id);
					
					ResultSet resultSet = statement.executeQuery();
					this.outlets = new ArrayList<CaseOutlet>();
					
					try {
						while(resultSet.next()){
							JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));
							
							Merchant m = new Merchant();
							m.setId(Long.parseLong(obj.get("merchant_id").toString()));
							m.setRegisteredName(obj.get("registered_name").toString());
							m.setRegisteredNo(obj.get("registered_no").toString());

							try{
								m.setGst_registration(obj.containsKey("gst_registration") ? obj.get("gst_registration").toString() : null);
							}catch (Exception e1){
								System.out.println("set gst_registration error");
							}
							try{
								m.setRmcd_approval_no(obj.containsKey("rmcd_approval_no") ? obj.get("rmcd_approval_no").toString() : null);
							}catch (Exception e1){
								System.out.println("set rmcd_approval_no error");
							}
							
							Outlet o = new Outlet();
							o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
							o.setName(obj.get("trading_name").toString());
							o.setLicenseNo(obj.get("license_no").toString());
							o.setLicenseType(obj.get("license_type").toString());
							o.setLicenseIssuer(obj.get("issuer").toString());
							o.setLicenseState(obj.get("state").toString());
							o.setLicenseExpiry(obj.get("expiry_date").toString());
							o.setRiskLevel(obj.get("risk_level").toString());
							o.setCTName(obj.get("contact_person").toString());
							o.setCTDesignation(obj.get("designation").toString());
							o.setCTEmail(obj.get("email").toString());
							o.setCTTel(obj.get("telephone_no").toString());
							o.setCTFax(obj.get("fax_no").toString());

							try{
								o.setGst_registration(obj.containsKey("gst_registration") ? obj.get("gst_registration").toString() : null);
							}catch (Exception e1){
								System.out.println("set gst_registration error");
							}
							try{
								o.setRmcd_approval_no(obj.containsKey("rmcd_approval_no") ? obj.get("rmcd_approval_no").toString() : null);
							}catch (Exception e1){
								System.out.println("set rmcd_approval_no error");
							}
							
							o.setPaymentOpt(resultSet.getString("co_pymt_option"));
							o.setStatementOpt(resultSet.getString("co_stmt_option"));
							o.setOtherInfo(resultSet.getString("co_other_info"));
							o.setAddress(resultSet.getString("co_address"));
							o.setAddress2(resultSet.getString("co_address_2"));
							o.setAddress3(resultSet.getString("co_address_3"));
							o.setMDRRate(resultSet.getString("co_mdr_rate"));
							
							o.setMerchant(m);
							
							
							CaseOutlet a = new CaseOutlet();
							a.setOutletDtl(resultSet.getString("co_outlet_details"));
							a.setESARefNo(resultSet.getString("co_esa_ref_no"));
							a.setNumTerminal(resultSet.getString("co_num_terminal"));
							a.setTrmRental(resultSet.getString("co_trm_rental"));
							a.setTrmOwner(resultSet.getString("co_trm_owner"));
							a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
							a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
							a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
							a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
							a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
							a.setChequeAmt(resultSet.getString("co_cheque_amt"));
							a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
							a.setChequeNo(resultSet.getString("co_cheque_no"));
							a.setChequeDate(resultSet.getString("co_cheque_date"));
							a.setRecommendation(resultSet.getString("co_recommendation"));
							
							a.setOutlet(o);
							
							this.outlets.add(a);
						}
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void loadHistories(String strEMerchant, int branch){
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String group_regex = "ug::";
				String user_regex = "uu::";
				
				String sql = "select casehist.*, tbluser.name "
						+ "from casehist "
						+ "left join tbluser on ch_created_by = tbluser.login_id "
						+ "where ch_case_id = ? ";
				
				if(branch > 1) {
					sql += "and ch_branch >= ? ";
				} else {
					sql += "and ch_branch = ? ";
				}
				
				sql += "order by 1 desc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, this.id);
					statement.setInt(2, branch);
					
					ResultSet resultSet = statement.executeQuery();
					
					if(branch == 1){
						this.histories = new ArrayList<TreeMap<String, String>>();
					} else {
						this.histories2 = new ArrayList<TreeMap<String, String>>();
					}
					
					ArrayList<SecGroupsDao> arlSecGroupList = (ArrayList<SecGroupsDao>) SecGroupHome.getInstance().findSecGroupList(strEMerchant);
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> histLog = new TreeMap<String, String>();
							histLog.put("id", resultSet.getString("ch_id"));
							histLog.put("action", resultSet.getString("ch_action"));
							histLog.put("status", resultSet.getString("ch_status"));
							histLog.put("last_status", resultSet.getString("ch_last_status"));
							
							String temp = resultSet.getString("ch_note");
							if(resultSet.wasNull()){
								temp = "";
							}
							
							histLog.put("note", temp);
							
							temp = resultSet.getString("ch_assigned_to");
							if(resultSet.wasNull() == true){
								temp = "";
							}
							else {
								JSONArray arr = (JSONArray) JSONValue.parse(temp);
								
								String sep = "";
								temp = "";
								
								for(Object s : arr){
									String s_item = s.toString();
									
									if(s_item.startsWith(user_regex)) {
										String[] a = s_item.split(user_regex);
										String userId = a[1];
										
										UserDao userdao = SecUsersHome.getInstance().getUser(strEMerchant, userId);
										s_item = userdao.getUserName();
										
									} else if(s_item.startsWith(group_regex)) {
										String[] a = s_item.split(group_regex);
										String group_code = a[1];
										
										s_item = "[" + group_code + "]";
										
										for(SecGroupsDao ug : arlSecGroupList){
											if(ug.getGroupCode().equals(group_code)){
												s_item = "[" + ug.getGroupName() + "]";
											}
										}
										
									}
									
									temp += sep + s_item;
									sep = ", ";
								}
							}
							
							histLog.put("assigned_to", temp);
							histLog.put("created_by", resultSet.getString("ch_created_by"));
							
							String fv = resultSet.getString("name");
							if(resultSet.wasNull() == false){
								histLog.put("created_by", resultSet.getString("name"));
							}
							
							histLog.put("created_date", Long.toString(((Timestamp) resultSet.getTimestamp("ch_created_date")).getTime()));
							
							if(branch == 1) {
								this.histories.add(histLog);
							}
							else if(branch == 2) {
								this.histories2.add(histLog);
							}
						}
						
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			
		} catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void loadDocuments(){
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select cd_id, cd_name, cd_type, cd_length, cd_ref_no, cd_created_by, cd_created_date "
					+ "from casedoc where cd_case_id = ? order by 1 desc";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, this.id);
					
					ResultSet resultSet = statement.executeQuery();
					
					this.documents = new ArrayList<TreeMap<String, String>>();
					
					try {
						while(resultSet.next()){
							TreeMap<String, String> docLog = new TreeMap<String, String>();
							docLog.put("id", resultSet.getString("cd_id"));
							docLog.put("name", resultSet.getString("cd_name"));
							docLog.put("type", resultSet.getString("cd_type"));
							docLog.put("length", resultSet.getString("cd_length"));
							docLog.put("ref_no", resultSet.getString("cd_ref_no"));
							docLog.put("created_by", resultSet.getString("cd_created_by"));
							docLog.put("created_date", Long.toString(((Timestamp) resultSet.getTimestamp("cd_created_date")).getTime()));
							
							this.documents.add(docLog);
						}
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void loadRecommendations(String mid_pos, String mid_amex){
		try{
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select co_recommendation from caseoutlet ";
				
				if(mid_pos.length() > 0) {
					sql += "where co_mid_pos = ? ";
					
					if(mid_amex.length() > 0) {
						sql += "or co_mid_amex = ? ";
					}
				}
				else if(mid_amex.length() > 0) {
					sql += "where co_mid_amex = ? ";
				}
				
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					if(mid_pos.length() > 0) {
						statement.setString(1, mid_pos);
						
						if(mid_amex.length() > 0) {
							statement.setString(2, mid_amex);
						}
					}
					else if(mid_amex.length() > 0) {
						statement.setString(1, mid_amex);
					}
					
					
					ResultSet resultSet = statement.executeQuery();
					
					this.recommendations = new JSONArray();
					
					try {
						while(resultSet.next()){
							if(resultSet.getString("co_recommendation") != null) {
								JSONObject obj = (JSONObject) JSONValue.parse(resultSet.getString("co_recommendation"));
								this.recommendations.add(obj);
							}
						}
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void setId(long v) {
		this.id = v;
	}
	
	public void setGroupCode(String v) {
		if(v != null)
			this.group_code = v;
	}
	
	public void setGroupName(String v) {
		if(v != null)
			this.group_name = v;
	}
	
	public void setType(String v) {
		if(v != null)
			this.type = v;
	}
	
	public void setAction(String v) {
		if(v != null)
			this.action = v;
	}
	
	public void setStatus(String v) {
		if(v != null)
			this.status = v;
	}
	
	public void setAction2(String v) {
		if(v != null)
			this.action2 = v;
	}
	
	public void setStatus2(String v) {
		if(v != null)
			this.status2 = v;
	}
	
	public void setBranch2(String v) {
		if(v != null)
			this.branch2 = v;
	}
	
	public void setAcquirer(String v) {
		if(v != null)
			this.acquirer = v;
	}
	
	public void setMerchant(Merchant v) {
		this.merchant = v;
	}
	
	public void setCreatedDate(long v){
		this.created_date = v;;
	}
	
	public void setCreatedBy(String v) {
		if(v != null)
			this.created_by = v;
	}
	
	public void setSubmitDate(long v){
		this.submit_date = v;;
	}
	
	public void setReferenceNo(String v) {
		if(v != null)
			this.reference_no = v;
	}
	
	public void setLastReferenceNo(String v) {
		if(v != null)
			this.last_reference_no = v;
	}
	
	public void setCountryCode(String v) {
		if(v != null)
			this.country_code = v;
	}
	
	public void setDocumentChecklist(String v) {
		if(v != null)
			this.document_checklist = v;
	}
	
	public void setBestPractice(String v) {
		if(v != null) 
			this.best_practice = v;
	}
	
	public void setExternalRemark(String v) {
		if(v != null) 
			this.external_remark = v;
	}
	
	public void setExternalRefNo(String v) {
		if(v != null) 
			this.external_ref_no = v;
	}
	
	public void setExternalCode(String v) {
		if(v != null) 
			this.external_code = v;
	}
	
	public void setExportedExternalCode(String v) {
		if(v != null) 
			this.exported_external_code = v;
	}
	
	public void setResellerLocation(String v) {
		if(v != null) 
			this.reseller_location = v;
	}
}